CREATE DATABASE ASPNETLibraryTest
GO
USE ASPNETLibraryTest
GO

CREATE TABLE [dbo].[Users](
	[UserID] INT IDENTITY(1,1) NOT NULL,
	[Username] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.GetUsersPaged
(
	@PageIndex		INT,
	@PageSize		INT
)
AS
	BEGIN
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int

	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize

	-- Create a temp table to store the select results
	Create Table #PageIndex
	(
		[IndexId] int IDENTITY (1, 1) NOT NULL,
		[UserID] int 
	)

	-- Insert into the temp table
	declare @SQL as nvarchar(4000)
	SET @SQL = 'INSERT INTO #PageIndex (UserID)'
	SET @SQL = @SQL + ' SELECT'
	IF @PageSize > 0
	BEGIN
		SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
	END
	SET @SQL = @SQL + ' [UserID]'
	SET @SQL = @SQL + ' FROM dbo.[Users]'

	-- Populate the temp table
	EXEC sp_executesql @SQL

	-- Return paged results
	SELECT U.[UserID], U.[Username], U.[FirstName], U.[LastName]
	FROM
		dbo.[Users] U,
		#PageIndex PageIndex
	WHERE
		PageIndex.IndexID > @PageLowerBound
		AND U.[UserID] = PageIndex.[UserID]
	ORDER BY
		PageIndex.IndexID

	-- get row count
	SELECT COUNT(UserID) as TotalRowCount
	FROM dbo.[Users]

	END
GO

DECLARE @index INT
SET @index = 0
WHILE (@index < 290) BEGIN
	DECLARE @Username NVARCHAR(50)
	DECLARE @FirstName NVARCHAR(50)
	DECLARE @LastName NVARCHAR(50)
	
	SET @Username = 'user' + CONVERT(NVARCHAR(3), @index)
	SET @FirstName = 'firstname' + CONVERT(NVARCHAR(3), @index)
	SET @LastName = 'lastname' + CONVERT(NVARCHAR(3), @index)
	
	INSERT INTO [dbo].[Users] (Username, FirstName, LastName)
	VALUES(@Username, @FirstName, @LastName)
	
	SET @index = @index + 1
END
GO